﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using com.vs.DataAccess;
using com.vs.General;

namespace com.vs.DataAccess.DO
{
    public partial class FilterStatementDO
    {
        #region "Get Model"
        /// <summary>
        /// 将符合数据库主键的持久对象提取到模型对象中，如数据库符合条件的持久对象不存在，则返回空对象
        /// </summary>
        /// <remarks></remarks>
        public static FilterStatementDO GetModelByPK(Session sess, string stRecordType, string stFilterName, int iSeq)
        {
            DataHelper helper = null;
            DataTable dtModel = null;
            string stSQL = SelectSequel + " WHERE RECORD_TYPE = @RECORD_TYPE AND FILTER_NAME = @FILTER_NAME AND SEQUENCE = @SEQUENCE "; ;
            try
            {
                helper = new DataHelper(sess);
                dtModel = helper.GetTable(CommandType.StoredProcedure, stSQL, new IDataParameter[]{
                    DataHelper.BuildParameter(sess, "@RECORD_TYPE", stRecordType),
                    DataHelper.BuildParameter(sess, "@FILTER_NAME", stFilterName),
                    DataHelper.BuildParameter(sess, "@SEQUENCE", iSeq)
                });
                if (!DataManager.IsEmpty(dtModel))
                {
                    return GetModel(dtModel.Rows[0]);
                }
                else
                {
                    return null;
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (helper != null)
                    helper.Dispose();
            }
        }

        #endregion

        #region "DataBase Operation"
        /// <summary>
        /// 在数据库里创建该数据访问对象的持久化数据
        /// </summary>
        /// <remarks></remarks>
        public int Insert(Session sess)
        {
            DataHelper helper = null;
            IDataParameter[] paras = null;
            string stSQL = "CFG_IFilterStatement";
            try
            {
                helper = new DataHelper(sess);
                paras = ValueParas(this);
                return helper.GetInt(CommandType.StoredProcedure, stSQL, paras);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (helper != null)
                    helper.Dispose();
            }
        }

        public void Delete(Session sess)
        {
            DataHelper helper = null;
            string stSQL = "DELETE FROM ALLOCATION_RULE_DETAIL WHERE OBJECT_ID = @OBJECT_ID";
            try
            {
                helper = new DataHelper(sess);
                helper.Update(CommandType.StoredProcedure, stSQL, UpdateWherePara(this));
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (helper != null)
                    helper.Dispose();
            }
        }

        /// <summary>
        /// 将该数据访问对象的变化同步到数据库中，如果该数据访问对象在数据库中的持久数据不存在，同步不会发生，同时也不会抛出例外
        /// </summary>
        /// <remarks></remarks>
        public void Update(Session sess)
        {
            DataHelper helper = null;
            string stSQL = "CFG_UFilterStatement";
            try
            {
                helper = new DataHelper(sess);
                helper.Update(CommandType.StoredProcedure, stSQL, ValueParas(this));
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (helper != null)
                    helper.Dispose();
            }
        }

        #endregion

        #region "Data Load"
        /// <summary>
        /// 装载对象ALLOCATIONRULEDETAIL的所有持久性对象到集合，如果数据量大，调用此方法会造成性能上的问题，请谨慎使用
        /// </summary>
        /// <remarks></remarks>
        public static List<FilterStatementDO> GetAll(Session sess)
        {
            DataHelper helper = null;
            DataTable dtModel = null;
            string stSQL = SelectSequel;
            try
            {
                helper = new DataHelper(sess);
                dtModel = helper.GetTable(CommandType.Text, stSQL);
                return LoadListFromDataView(dtModel.DefaultView);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (helper != null)
                    helper.Dispose();
            }
        }

        public static DataTable GetAllToTable(Session sess)
        {
            DataHelper helper = null;
            string stSQL = SelectSequel;
            try
            {
                helper = new DataHelper(sess);
                return helper.GetTable(CommandType.Text, stSQL);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (helper != null)
                    helper.Dispose();
            }
        }

        /// <summary>
        /// 装载指定列的值等于指定值的的所有持久性ALLOCATIONRULEDETAIL对象到集合
        /// </summary>
        /// <remarks></remarks>
        public static List<FilterStatementDO> GetListByColumn(Session sess, string columnName, Object value)
        {
            DataHelper helper = null;
            DataTable dtModel = null;
            IDataParameter[] paras = null;
            string stSQL = null;
            try
            {
                helper = new DataHelper(sess);
                stSQL = SelectSequel + " Where [" + columnName + "] =@Value ";
                paras = new IDataParameter[]{
                    new SqlParameter( "@Value", value)
                };
                dtModel = helper.GetTable(CommandType.Text, stSQL, paras);
                return LoadListFromDataView(dtModel.DefaultView);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (helper != null)
                    helper.Dispose();
            }

        }

        /// <summary>
        /// 从DataView装载持久性ALLOCATIONRULEDETAIL对象到集合
        /// </summary>
        /// <remarks></remarks>
        private static List<FilterStatementDO> LoadListFromDataView(DataView dv)
        {
            List<FilterStatementDO> list = new List<FilterStatementDO>();
            for (int index = 0; index <= dv.Count - 1; index++)
            {
                list.Add(GetModel(dv[index].Row));
            }
            return list;
        }
        /// <summary>
        /// loading data value  to property from Datarow
        /// </summary>
        /// <remarks></remarks>
        private static FilterStatementDO GetModel(DataRow row)
        {
            FilterStatementDO obj = new FilterStatementDO();
            if (!row["RECORD_TYPE"].Equals(DBNull.Value)) obj.RECORD_TYPE = Convert.ToString(row["RECORD_TYPE"]);
            if (!row["FILTER_NAME"].Equals(DBNull.Value)) obj.FILTER_NAME = Convert.ToString(row["FILTER_NAME"]);
            if (!row["SEQUENCE"].Equals(DBNull.Value)) obj.SEQUENCE = Convert.ToInt32(row["SEQUENCE"]);
            if (!row["AND_OR"].Equals(DBNull.Value)) obj.AND_OR = Convert.ToString(row["AND_OR"]);
            if (!row["ATTRIBUTE"].Equals(DBNull.Value)) obj.ATTRIBUTE = Convert.ToString(row["ATTRIBUTE"]);
            if (!row["OPERAND"].Equals(DBNull.Value)) obj.OPERAND = Convert.ToString(row["OPERAND"]);
            if (!row["LITERAL_VALUE"].Equals(DBNull.Value)) obj.LITERAL_VALUE = Convert.ToString(row["LITERAL_VALUE"]);
            if (!row["USER_STAMP"].Equals(DBNull.Value)) obj.USER_STAMP = Convert.ToString(row["USER_STAMP"]);
            if (!row["DATE_TIME_STAMP"].Equals(DBNull.Value)) obj.DATE_TIME_STAMP = Convert.ToDateTime(row["DATE_TIME_STAMP"]);
            if (!row["LEFT_PAREN"].Equals(DBNull.Value)) obj.LEFT_PAREN = Convert.ToInt32(row["LEFT_PAREN"]);
            if (!row["RIGHT_PAREN"].Equals(DBNull.Value)) obj.RIGHT_PAREN = Convert.ToInt32(row["RIGHT_PAREN"]);
            if (!row["OBJECT_ID"].Equals(DBNull.Value)) obj.OBJECT_ID = Convert.ToInt32(row["OBJECT_ID"]);
            if (!row["LITERAL_TYPE"].Equals(DBNull.Value)) obj.LITERAL_TYPE = Convert.ToString(row["LITERAL_TYPE"]);


            return obj;
        }

        #endregion

        #region "Other function"
        /// <summary>
        /// 该数据访问对象从数据库中提取数据的Sql语句 
        /// </summary>
        /// <remarks></remarks>
        private static string SelectSequel
        {
            get
            {
                return "Select	*  From [FILTER_STATEMENT] ";
            }
        }

        /// <summary>
        /// 该数据访问对象的属性值装载到数据库更新参数数组
        /// </summary>
        /// <remarks></remarks>
        private static IDbDataParameter[] ValueParas(FilterStatementDO obj)
        {
            SqlParameter[] paras = new SqlParameter[13];
            paras[0] = new SqlParameter("@RECORD_TYPE", obj.RECORD_TYPE == String.Empty ? DBNull.Value : (object)obj.RECORD_TYPE);
            paras[0].DbType = DbType.String;
            paras[1] = new SqlParameter("@FILTER_NAME", obj.FILTER_NAME == String.Empty ? DBNull.Value : (object)obj.FILTER_NAME);
            paras[1].DbType = DbType.String;
            paras[2] = new SqlParameter("@SEQUENCE", obj.SEQUENCE);
            paras[2].DbType = DbType.String;
            paras[3] = new SqlParameter("@AND_OR", obj.AND_OR == String.Empty ? DBNull.Value : (object)obj.AND_OR);
            paras[3].DbType = DbType.String;
            paras[4] = new SqlParameter("@ATTRIBUTE", obj.ATTRIBUTE == String.Empty ? DBNull.Value : (object)obj.ATTRIBUTE);
            paras[4].DbType = DbType.String;
            paras[5] = new SqlParameter("@OPERAND", obj.OPERAND == String.Empty ? DBNull.Value : (object)obj.OPERAND);
            paras[5].DbType = DbType.String;
            paras[6] = new SqlParameter("@LITERAL_VALUE", obj.LITERAL_VALUE == String.Empty ? DBNull.Value : (object)obj.LITERAL_VALUE);
            paras[6].DbType = DbType.String;
            paras[7] = new SqlParameter("@USER_STAMP", obj.USER_STAMP == String.Empty ? DBNull.Value : (object)obj.USER_STAMP);
            paras[7].DbType = DbType.String;
            paras[8] = new SqlParameter("@DATE_TIME_STAMP", obj.DATE_TIME_STAMP == DateTime.MinValue ? DBNull.Value : (object)obj.DATE_TIME_STAMP);
            paras[8].DbType = DbType.DateTime;
            paras[9] = new SqlParameter("@LEFT_PAREN", obj.LEFT_PAREN);
            paras[9].DbType = DbType.Decimal;
            paras[10] = new SqlParameter("@RIGHT_PAREN", obj.RIGHT_PAREN);
            paras[10].DbType = DbType.Decimal;
            paras[11] = new SqlParameter("@OBJECT_ID", obj.OBJECT_ID);
            paras[11].DbType = DbType.Decimal;
            paras[12] = new SqlParameter("@LITERAL_TYPE", obj.LITERAL_VALUE == String.Empty ? DBNull.Value : (object)obj.LITERAL_TYPE);
            paras[12].DbType = DbType.String;

            return paras;
        }

        private static IDbDataParameter[] UpdateWherePara(FilterStatementDO obj)
        {
            SqlParameter[] paras = new SqlParameter[3];
            paras[0] = new SqlParameter("@RECORD_TYPE", obj.RECORD_TYPE);
            paras[1] = new SqlParameter("@FILTER_NAME", obj.FILTER_NAME);
            paras[2] = new SqlParameter("@SEQUENCE", obj.SEQUENCE);


            return paras;
        }

        #endregion

        #region GetAllocRuleSequences
        ///<summary> This method retrieves the Allocation Rule details given an Allocation Rule.
        ///</summary>
        ///
        ///<param name="session">Session</param>
        ///<param name="rule">The Allocation rule name</param>
        ///
        ///<returns>Array of Sequences with that Allocation Rule</returns>
        public static int[] GetAllocRuleSequences(Session session, string allocRule)
        {
            try
            {
                string queryBuilder = "select " +
                    "SEQUENCE " +
                    "from " +
                    "ALLOCATION_RULE_DETAIL " +
                    "where " +
                    "ALLOCATION_RULE = @allocRule " +
                    "order by " +
                    "SEQUENCE";

                int[] allocRuleSeq = null;
                DataTable dTable;
                using (DataHelper helper = new DataHelper(session))
                {
                    dTable = helper.GetTable(CommandType.Text, queryBuilder,
                        helper.BuildParameter("@allocRule", allocRule)
                        );
                }

                if (!DataManager.IsEmpty(dTable))
                {
                    allocRuleSeq = new int[dTable.Rows.Count];
                    for (int i = 0; i < dTable.Rows.Count; i++)
                    {
                        allocRuleSeq[i] = DataManager.GetInt(dTable, i, "SEQUENCE");
                    }
                }

                return allocRuleSeq;

            }
            finally
            {
            }

        }
        #endregion GetAllocRuleSequences

        #region GetAllocationRule
        ///<summary> Get an allocation rule from the cache
        ///</summary>
        ///
        ///<param name="session">Session</param>
        ///<param name="rule">rule to retrieve</param>
        ///<param name="sequence">sequence</param>
        ///
        ///<returns>Datarow for the allocation rule</returns>
        public static DataRow GetAllocationRule(Session session, string rule, int sequence)
        {
            try
            {
                string queryBuilder = "select * from " +
                    "ALLOCATION_RULE_DETAIL " +
                    "where " +
                    "ALLOCATION_RULE = @allocName " +
                    "and " +
                    "SEQUENCE = @sequence ";

                DataRow allocRule;
                using (DataHelper helper = new DataHelper(session))
                {
                    allocRule = helper.GetRow(CommandType.Text, queryBuilder,
                        helper.BuildParameter("@allocName", rule),
                        helper.BuildParameter("@sequence", sequence)
                        );
                }

                return allocRule;

            }
            finally
            {
            }

        }
        #endregion GetAllocationRule



        public static List<FilterStatementDO> GetListByColumns(Session sess, List<KeyValuePair<string, KeyValuePair<string, object>>> dcParam, string stOrderBy)
        {
            DataTable dtModel = null;

            try
            {
                dtModel = GetTableByColumns(sess, dcParam, stOrderBy);
                return LoadListFromDataView(dtModel.DefaultView);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
            }
        }

        public static DataTable GetTableByColumns(Session sess, List<KeyValuePair<string, KeyValuePair<string, object>>> dcParam, string stOrderBy)
        {
            DataHelper helper = null;
            StringBuilder sbSQL = null;
            int i = 0;
            IDataParameter[] pars = null;

            try
            {
                helper = new DataHelper(sess);
                sbSQL = new StringBuilder(SelectSequel)
                    .Append(" WHERE 1=1 ");

                pars = new IDataParameter[dcParam.Count];
                foreach (KeyValuePair<string, KeyValuePair<string, object>> de in dcParam)
                {
                    sbSQL.Append(" AND ").Append(de.Key).Append(de.Value.Key).Append("@Value").Append(i);
                    pars[i] = new SqlParameter("@Value" + i, de.Value.Value);
                    i++;
                }

                if (!StringManager.IsEmpty(stOrderBy))
                {
                    sbSQL.Append(stOrderBy);
                }
                return helper.GetTable(CommandType.Text, sbSQL.ToString(), pars);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (helper != null)
                    helper.Dispose();
            }
        }
    }
}
